package com.idea.dsl.service;

import lombok.Data;
import lombok.RequiredArgsConstructor;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Excel处理服务
 */
@Slf4j
@Service
@RequiredArgsConstructor
public class ExcelProcessingService {
    
    /**
     * 解析Excel文件为业务规则数据
     */
    public List<BusinessRuleData> parseExcelToBusinessRules(MultipartFile file) throws IOException {
        log.info("开始解析Excel文件: {}", file.getOriginalFilename());
        
        List<BusinessRuleData> rules = new ArrayList<>();
        
        try (InputStream inputStream = file.getInputStream()) {
            Workbook workbook = createWorkbook(file.getOriginalFilename(), inputStream);
            
            // 默认读取第一个工作表
            Sheet sheet = workbook.getSheetAt(0);
            
            // 查找表头行
            int headerRowIndex = findHeaderRow(sheet);
            if (headerRowIndex == -1) {
                throw new IllegalArgumentException("未找到有效的表头行");
            }
            
            Row headerRow = sheet.getRow(headerRowIndex);
            Map<String, Integer> columnMapping = createColumnMapping(headerRow);
            
            // 读取数据行
            for (int i = headerRowIndex + 1; i <= sheet.getLastRowNum(); i++) {
                Row row = sheet.getRow(i);
                if (row == null || isEmptyRow(row)) {
                    continue;
                }
                
                BusinessRuleData ruleData = parseRowToBusinessRule(row, columnMapping);
                if (ruleData != null && isValidBusinessRule(ruleData)) {
                    rules.add(ruleData);
                }
            }
            
            workbook.close();
        }
        
        log.info("Excel解析完成，共解析出 {} 条业务规则", rules.size());
        return rules;
    }
    
    /**
     * 解析Excel文件为通用数据
     */
    public List<Map<String, String>> parseExcelToGenericData(MultipartFile file) throws IOException {
        log.info("开始解析Excel文件为通用数据: {}", file.getOriginalFilename());
        
        List<Map<String, String>> dataList = new ArrayList<>();
        
        try (InputStream inputStream = file.getInputStream()) {
            Workbook workbook = createWorkbook(file.getOriginalFilename(), inputStream);
            Sheet sheet = workbook.getSheetAt(0);
            
            // 查找表头行
            int headerRowIndex = findHeaderRow(sheet);
            if (headerRowIndex == -1) {
                throw new IllegalArgumentException("未找到有效的表头行");
            }
            
            Row headerRow = sheet.getRow(headerRowIndex);
            List<String> headers = new ArrayList<>();
            
            // 读取表头
            for (int i = 0; i < headerRow.getLastCellNum(); i++) {
                Cell cell = headerRow.getCell(i);
                String header = getCellValueAsString(cell);
                headers.add(header != null ? header.trim() : "");
            }
            
            // 读取数据行
            for (int i = headerRowIndex + 1; i <= sheet.getLastRowNum(); i++) {
                Row row = sheet.getRow(i);
                if (row == null || isEmptyRow(row)) {
                    continue;
                }
                
                Map<String, String> rowData = new HashMap<>();
                for (int j = 0; j < headers.size() && j < row.getLastCellNum(); j++) {
                    Cell cell = row.getCell(j);
                    String value = getCellValueAsString(cell);
                    rowData.put(headers.get(j), value != null ? value.trim() : "");
                }
                
                dataList.add(rowData);
            }
            
            workbook.close();
        }
        
        log.info("Excel解析完成，共解析出 {} 行数据", dataList.size());
        return dataList;
    }
    
    /**
     * 创建工作簿
     */
    private Workbook createWorkbook(String filename, InputStream inputStream) throws IOException {
        if (filename.toLowerCase().endsWith(".xlsx")) {
            return new XSSFWorkbook(inputStream);
        } else if (filename.toLowerCase().endsWith(".xls")) {
            return new HSSFWorkbook(inputStream);
        } else {
            throw new IllegalArgumentException("不支持的文件格式，请使用.xls或.xlsx文件");
        }
    }
    
    /**
     * 查找表头行
     */
    private int findHeaderRow(Sheet sheet) {
        // 在前10行中查找包含"规则名称"或"ruleName"的行
        for (int i = 0; i < Math.min(10, sheet.getLastRowNum() + 1); i++) {
            Row row = sheet.getRow(i);
            if (row == null) continue;
            
            for (Cell cell : row) {
                String value = getCellValueAsString(cell);
                if (value != null) {
                    value = value.toLowerCase().trim();
                    if (value.contains("规则名称") || value.contains("rulename") || 
                        value.contains("关联属性") || value.contains("relatedfield") ||
                        value.contains("规则描述") || value.contains("ruledescription")) {
                        return i;
                    }
                }
            }
        }
        
        // 如果没找到特定标识，默认第一行为表头
        return 0;
    }
    
    /**
     * 创建列映射
     */
    private Map<String, Integer> createColumnMapping(Row headerRow) {
        Map<String, Integer> mapping = new HashMap<>();
        
        for (int i = 0; i < headerRow.getLastCellNum(); i++) {
            Cell cell = headerRow.getCell(i);
            String header = getCellValueAsString(cell);
            if (header != null) {
                header = header.trim().toLowerCase();
                
                // 规则名称列
                if (header.contains("规则名称") || header.contains("rulename") || header.equals("a")) {
                    mapping.put("ruleName", i);
                }
                // 关联字段列
                else if (header.contains("关联属性") || header.contains("关联字段") || 
                         header.contains("relatedfield") || header.equals("b")) {
                    mapping.put("relatedField", i);
                }
                // 规则描述列
                else if (header.contains("规则描述") || header.contains("规则编号") ||
                         header.contains("ruledescription") || header.equals("c")) {
                    mapping.put("ruleDescription", i);
                }
            }
        }
        
        return mapping;
    }
    
    /**
     * 解析行数据为业务规则
     */
    private BusinessRuleData parseRowToBusinessRule(Row row, Map<String, Integer> columnMapping) {
        BusinessRuleData ruleData = new BusinessRuleData();
        
        // 规则名称
        Integer ruleNameCol = columnMapping.get("ruleName");
        if (ruleNameCol != null) {
            ruleData.setRuleName(getCellValueAsString(row.getCell(ruleNameCol)));
        }
        
        // 关联字段
        Integer relatedFieldCol = columnMapping.get("relatedField");
        if (relatedFieldCol != null) {
            ruleData.setRelatedField(getCellValueAsString(row.getCell(relatedFieldCol)));
        }
        
        // 规则描述
        Integer ruleDescCol = columnMapping.get("ruleDescription");
        if (ruleDescCol != null) {
            ruleData.setRuleDescription(getCellValueAsString(row.getCell(ruleDescCol)));
        }
        
        return ruleData;
    }
    
    /**
     * 获取单元格值为字符串
     */
    private String getCellValueAsString(Cell cell) {
        if (cell == null) {
            return null;
        }
        
        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    return cell.getDateCellValue().toString();
                } else {
                    return String.valueOf((long) cell.getNumericCellValue());
                }
            case BOOLEAN:
                return String.valueOf(cell.getBooleanCellValue());
            case FORMULA:
                return cell.getCellFormula();
            case BLANK:
                return "";
            default:
                return "";
        }
    }
    
    /**
     * 检查是否为空行
     */
    private boolean isEmptyRow(Row row) {
        for (Cell cell : row) {
            String value = getCellValueAsString(cell);
            if (value != null && !value.trim().isEmpty()) {
                return false;
            }
        }
        return true;
    }
    
    /**
     * 验证业务规则数据是否有效
     */
    private boolean isValidBusinessRule(BusinessRuleData ruleData) {
        return ruleData.getRuleName() != null && !ruleData.getRuleName().trim().isEmpty() &&
               ruleData.getRuleDescription() != null && !ruleData.getRuleDescription().trim().isEmpty();
    }
    
    /**
     * 业务规则数据模型
     */
    @Data
    public static class BusinessRuleData {
        private String ruleName;
        private String relatedField;
        private String ruleDescription;
    }
}
